<?php


namespace app\web\controller;


use think\Controller;
use think\Db;
use think\facade\Request;

class Sjjc extends Controller
{
    public function init()
    {
        return Db::query("
            select n.year as year,p.Num as '正面评论',n.num as '负面评论' from(
                select year(ReleaseDate)as year,Count(Pid) as num
                from comments
                WHERE `Emotional`='满意'
                group by year(ReleaseDate)
            )as p,(
                select year(ReleaseDate)as year,Count(Pid) as num
                from comments
                WHERE `Emotional`='不满意'
                group by year(ReleaseDate)
            )as n
            where p.year=n.year");
    }

    public function update()
    {
        $models = Request::param("carModel");
        $themes = Request::param("Theme");
        $startDate = Request::param("startDate");
        $endDate = Request::param("endDate");

        $dateInterval = strtotime($endDate) - strtotime($startDate);

        //搜索所选车型
        $whereModel = $this->carModel($models);
        //搜索所选主题
        $whereTheme = $this->theme($themes);
        //区间超过两年就按年返回
        if ($dateInterval > 63072000) {
            $sql1 = "(select DATE_FORMAT(ReleaseDate,'%Y') as days,count(Pid) as count from comments where ReleaseDate>'"
                . $startDate . "' and ReleaseDate<'" . $endDate . "' "
                . $whereModel . $whereTheme . "and Emotional='满意' "
                . " group by days) ";
            $sql2 = "(select DATE_FORMAT(ReleaseDate,'%Y') as days,count(Pid) as count from comments where ReleaseDate>'"
                . $startDate . "' and ReleaseDate<'" . $endDate . "' "
                . $whereModel . $whereTheme . "and Emotional='不满意' "
                . " group by days) ";
            $sql = "select p.days as 'year',n.count as '负面评论',p.count as '正面评论' from " . $sql1 . "as p," . $sql2 . "as n where n.days=p.days";
        } //区间少于两年就按月返回
        else {
            $sql1 = "(select DATE_FORMAT(ReleaseDate,'%Y-%m') as days,count(Pid) as count from comments where ReleaseDate>'"
                . $startDate . "' and ReleaseDate<'" . $endDate . "' "
                . $whereModel . $whereTheme . "and Emotional='满意' "
                . " group by days) ";
            $sql2 = "(select DATE_FORMAT(ReleaseDate,'%Y-%m') as days,count(Pid) as count from comments where ReleaseDate>'"
                . $startDate . "' and ReleaseDate<'" . $endDate . "' "
                . $whereModel . $whereTheme . "and Emotional='不满意' "
                . " group by days) ";
            $sql = "select p.days as 'year',n.count as '负面评论' ,p.count as '正面评论'from " . $sql1 . "as p," . $sql2 . "as n where n.days=p.days";
        }


//        return json($models);
//        return $sql;
//        return json(Db::query($sql));
        return Db::query($sql);
    }

    public function wordsCloudInit()
    {
        $sql = "select KeyWords,COUNT(KeyWords) as count from comments WHERE KeyWords is NOT null GROUP BY KeyWords ORDER BY count DESC LIMIT 100";
        return Db::query($sql);
    }

    public function wordsCloudUpdate()
    {
        $models = Request::param("carModel");
        $themes = Request::param("Theme");
        $startDate = Request::param("startDate");
        $endDate = Request::param("endDate");
        //搜索所选车型
        $whereModel = $this->carModel($models);
        //搜索所选主题
        $whereTheme = $this->theme($themes);

        $sql = "select KeyWords,COUNT(KeyWords) as count from comments WHERE KeyWords is NOT null and ReleaseDate>'"
            . $startDate . "' and ReleaseDate<'" . $endDate . "' "
            . $whereModel . $whereTheme . "GROUP BY KeyWords ORDER BY count DESC LIMIT 100";
        return Db::query($sql);
    }

    /**
     * 返回where查找车型部分
     * @param $data
     * @return string
     */
    public function carModel($data)
    {
        if ($data == null)
            return "";
        else {
            $s = "and (";
            foreach ($data as $key => $value)
                $s = $s . "CarModel like '%" . $value . "%' or ";
            $s = substr($s, 0, strlen($s) - 3);
            return $s . ") ";
        }
    }

    /**
     * 返回where查找主题部分
     * @param $data
     * @return string
     */
    public function theme($data)
    {
        if ($data == null)
            return "";
        else {
            $s = "and (";
            foreach ($data as $key => $value)
                $s = $s . "Theme='" . $value . "' or ";
            $s = substr($s, 0, strlen($s) - 3);
            return $s . ") ";
        }
    }
}